﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Configuration;
using System.IO;

namespace QD.Snapshot.Core.DAL
{
    /// <summary>
    /// SQL Server操作类
    /// 2013-07-10 wanghui
    /// </summary>
    public sealed class SqlHelper2013
    {
        private SqlHelper2013()
        {

        }
        public static string CONN_STRING = QD.Snapshot.Core.DAL.DAO.conString;

        public static SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(CONN_STRING);
            SqlDataReader rdr = null;
            try
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
            }
            catch (Exception ex)
            {
                conn.Close();
                Pigeon.Utility.Log.Write(string.Format("{0}_{1}.log", "db_error", DateTime.Now.ToString("yyyyMMdd")), string.Format("{0} sql:{1}", ex.Message, cmdText));
            }
            return rdr;
        }

        public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(connectionString);
            SqlDataReader rdr = null;
            try
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
            }
            catch (Exception ex)
            {
                conn.Close();
                Pigeon.Utility.Log.Write(string.Format("{0}_{1}.log", "db_error", DateTime.Now.ToString("yyyyMMdd")), string.Format("{0} sql:{1}", ex.Message, cmdText));
            }
            return rdr;
        }

        public static object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            object val = "";
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection conn = new SqlConnection(CONN_STRING))
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                try
                {
                    val = cmd.ExecuteScalar();
                }
                catch (Exception ex)
                {
                    Pigeon.Utility.Log.Write(string.Format("{0}_{1}.log", "db_error", DateTime.Now.ToString("yyyyMMdd")), string.Format("{0} sql:{1}", ex.Message, cmdText));
                }
                finally
                {
                    cmd.Parameters.Clear();
                    cmd.Dispose();
                    conn.Close();
                    conn.Dispose();
                }
                return val;
            }
        }

        public static object ExecuteScalar(SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            object val = "";
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
            try
            {
                val = cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                Pigeon.Utility.Log.Write(string.Format("{0}_{1}.log", "db_error", DateTime.Now.ToString("yyyyMMdd")), string.Format("{0} sql:{1}", ex.Message, cmdText));
            }
            finally
            {
                cmd.Parameters.Clear();
                cmd.Dispose();
                conn.Close();
                conn.Dispose();
            }
            return val;
        }

        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
        {

            try
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                cmd.Connection = conn;
                cmd.CommandText = cmdText;

                if (trans != null)
                    cmd.Transaction = trans;

                cmd.CommandType = cmdType;

                if (cmdParms != null)
                {
                    foreach (SqlParameter parm in cmdParms)
                        cmd.Parameters.Add(parm);
                }
            }
            catch (Exception ex)
            {
                Pigeon.Utility.Log.Write(string.Format("{0}_{1}.log", "db_error", DateTime.Now.ToString("yyyyMMdd")), string.Format("{0}:PrepareCommand sql:{1}", ex.Message,cmdText));
            }
        }

        public static int ExecuteNonQuery(string cmdText, params SqlParameter[] cmdParms)
        {
            return ExecuteNonQuery(CommandType.Text, cmdText, cmdParms);
        }

        public static int ExecuteNonQuery(CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            int count = 0;
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection conn = new SqlConnection(CONN_STRING))
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                try
                {
                    count = cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    Pigeon.Utility.Log.Write(string.Format("{0}_{1}.log", "db_error", DateTime.Now.ToString("yyyyMMdd")), string.Format("{0} sql:{1}", ex.Message, cmdText));
                }
                finally
                {
                    cmd.Parameters.Clear();
                    cmd.Dispose();
                    conn.Close();
                    conn.Dispose();
                }
                return count;
            }
        }


        public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            int count = 0;
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                try
                {
                    count = cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    Pigeon.Utility.Log.Write(string.Format("{0}_{1}.log", "db_error", DateTime.Now.ToString("yyyyMMdd")), string.Format("{0} sql:{1}", ex.Message, cmdText));
                }
                finally
                {
                    cmd.Parameters.Clear();
                    cmd.Dispose();
                    conn.Close();
                    conn.Dispose();
                }
                return count;
            }
        }

        public static DataTable ExecuteReSult(CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection conn = new SqlConnection(CONN_STRING))
            {
                using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                {
                    PrepareCommand(adapter.SelectCommand, conn, null, cmdType, cmdText, cmdParms);
                    try
                    {
                        adapter.Fill(dt);
                    }
                    catch (Exception ex)
                    {
                        Pigeon.Utility.Log.Write(string.Format("{0}_{1}.log", "db_error", DateTime.Now.ToString("yyyyMMdd")), string.Format("{0} sql:{1}", ex.Message, cmdText));
                    }
                    finally
                    {
                        adapter.SelectCommand.Parameters.Clear();
                        cmd.Dispose();
                        conn.Close();
                        conn.Dispose();
                    }
                }
            }
            return dt;
        }


        public static bool SetString()
        {
            if (!File.Exists(AppDomain.CurrentDomain.BaseDirectory + "connection.config"))
                return false;
            try
            {
                using (StreamReader sr = new StreamReader(AppDomain.CurrentDomain.BaseDirectory + "connection.config"))
                {
                    while (sr.Peek() > -1)
                        CONN_STRING = sr.ReadToEnd();
                }
            }
            catch { return false; }
            return true;
        }
    }
}
